USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[PlayerVictimsSelectByPlayerIdLogId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.PlayerVictimsSelectByPlayerIdLogId
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 10/30/08
-- Description:	Player Victims Select By PlayerId and LogId
-- =============================================
CREATE PROCEDURE PlayerVictimsSelectByPlayerIdLogId

			@Attacker_playerId varchar(30),
			@LogId varchar(8000)
	
AS
BEGIN

DECLARE @Victim_playerId varchar(30)

DECLARE @PlayerName varchar(50),
		@Kills int,
		@Deaths int

DECLARE @tempPlayers TABLE
(
	PlayerName varchar(50),
	Kills int,
	Deaths int,
	KillDeathRatio varchar(5),
	Victim_playerId varchar(30)
)

DECLARE c1 CURSOR FAST_FORWARD
FOR
SELECT Victim_playerId from KILLS
WHERE Attacker_playerId = @Attacker_playerId
GROUP BY Victim_playerId

OPEN c1

FETCH NEXT FROM c1
INTO @Victim_playerId


WHILE @@FETCH_STATUS = 0
BEGIN

	SELECT @PlayerName = PlayerName FROM 
	(SELECT COUNT(PlayerName) AS Frequency, PlayerName, PlayerId as PlayerID 
		FROM PlayerNameList
		GROUP BY PlayerName, PlayerId ) as NewTable
	WHERE PlayerId = @Victim_playerId 
	GROUP BY PlayerName


	DECLARE @ResultSet TABLE (resultCount int)
	DECLARE @SQL varchar(600)

	-- KILLS
	SET @SQL = 'SELECT COUNT(KillId) FROM Kills WHERE Victim_PlayerId = ''' + @Victim_playerId + '''
					AND Attacker_playerId = ''' + @Attacker_playerId + '''
				AND LogId IN (' + @LogId + ')'

	DELETE FROM @ResultSet
	INSERT INTO @ResultSet EXEC(@SQL)
	SELECT @Kills = resultCount FROM @ResultSet

	-- Deaths
	SET @SQL = 'SELECT COUNT(KillId) FROM Kills WHERE Victim_PlayerId = ''' + @Attacker_playerId + '''
					AND Attacker_playerId = ''' + @Victim_playerId + '''
					AND LogId IN (' + @LogId + ')'

	DELETE FROM @ResultSet
	INSERT INTO @ResultSet EXEC(@SQL)
	select @Deaths = resultCount FROM @ResultSet



	IF (@Kills > 0 OR @Deaths > 0)
	BEGIN
		INSERT INTO @tempPlayers VALUES (@PlayerName, @Kills, @Deaths, '', @Victim_playerId)
	END

	FETCH NEXT FROM c1
	INTO @Victim_playerId

END

CLOSE c1
DEALLOCATE c1

SELECT PlayerName, Kills, Deaths, KillDeathRatio, Victim_playerId FROM @tempPlayers


END
GO

-- PlayerVictimsSelectByPlayerIdLogId 'STEAM_0:1:100283', '1,3'